﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Zor_Modes;
using Zor_IDAL;
using Zor_Tools;
using System.Data;
namespace Zor_DAL
{
    public class Zor_Certificate_DAL<T> where T : class
    {
        /// <summary>
        /// 查询所有级别为3(街道)的部门
        /// create liuqi by 2017.3.29     
        /// </summary>
        /// <returns></returns>
        public static List<T> GetStreetOffice()
        {
            var sql = "select[Dt_Id],[Dt_Name] from [dbo].[Sys_Department] where Dt_ParentId = "+ RequestSession.GetSessionUser().DT_Id +" ";
            return DapperQuery<T>.DoQueryList(sql, null);
        }

        /// <summary>
        /// 新增完税证领用信息
        /// create zhl by 2017.4.10 当前完税证号初始默认起始号
        /// </summary>
        /// <param name="certificate">完税证领用对象</param>
        /// <returns></returns>
        public static BaseAnswer InsertCertificate(Zor_Certificate certificate)
        {
            var dt_Id =RequestSession.GetSessionUser().DT_Id;
            var sql = @"if not exists(select Certificate_Zigui from Zor_Certificate where Certificate_Zigui =@Certificate_Zigui and user_Id =(select User_Id from Sys_Users where DT_Id="+dt_Id+" ) and Certificate_state =1) ";
                sql+="insert into Zor_Certificate values ";
                sql+= "(@Certificate_Zigui,@Certificate_CollarMan,@Certificate_StartTaxNum,@Certificate_EndTaxNum,@Certificate_state,@User_Id,@Certificate_CreateTime,@Certificate_ModifyTime,@Certificate_ModifyName,@Certificate_Remarks,@Certificate_FillIn,@Certificate_Invalid,@Certificate_Balance,@Certificate_NowTaxNum)";
            BaseAnswer bar = DapperInsert<T>.DoInsert(sql, certificate);
            return bar;
        }

        /// <summary>
        /// 修改完税证领用信息
        /// create zhl by 2017.4.10 修改加判断状态为未启用时才可以修改,当前完税证号初始默认起始号
        /// </summary>
        /// <param name="certificate">完税证领用对象</param>
        /// <returns></returns>
        public static BaseAnswer AlterCertificate(Zor_Certificate certificate)
        {
            var sql = "";
            sql = @"if not exists(select Certificate_Id	 from  Zor_Certificate	 where Certificate_Id=@Certificate_Id and Certificate_state   in(1,3) and Certificate_NowTaxNum between  Certificate_StartTaxNum and Certificate_EndTaxNum)";           
            sql += @"update [Zor_Certificate] set Certificate_Zigui=@Certificate_Zigui,Certificate_StartTaxNum=@Certificate_StartTaxNum,Certificate_EndTaxNum=@Certificate_EndTaxNum, 
                   Certificate_CollarMan=@Certificate_CollarMan,Certificate_ModifyTime=@Certificate_ModifyTime,Certificate_ModifyName=@Certificate_ModifyName,Certificate_NowTaxNum=@Certificate_NowTaxNum
                   where Certificate_Id = @Certificate_Id and  Certificate_state=@Certificate_state";
            BaseAnswer bar = DapperEdit<T>.DoEdit(sql, certificate);
            return bar;
        }

        /// <summary>
        /// 删除完税证领用信息-已启用信息不能删除
        /// create zhl by 2017.4.10 修改状态已启用已用完的不能删除
        /// </summary>
        /// <param name="id">删除的Id</param>
        /// <returns></returns>
        public static BaseAnswer DeleteCertificate(int[] id)
        {
            var sql = @"if not exists(select Certificate_state from Zor_Certificate where  Certificate_state in(1,3) and Certificate_Id in @Certificate_Id)
                      delete from Zor_Certificate where Certificate_Id in @Certificate_Id";
            BaseAnswer bar = DapperDelete<T>.DoDelete(sql, new { Certificate_Id = id.ToArray<int>() });
            return bar;
        }

        /// <summary>
        /// 停用完税证领用信息-(未启用:0,已启用:1,已停用:2,已用完:3)
        /// create liuqi by 2017.3.29
        /// </summary>
        /// <param name="id">停用的Id</param>
        /// <returns></returns>
        public static BaseAnswer StopCertificate(int[] id)
        {
            var sql = @"if not exists(select Certificate_state from Zor_Certificate where Certificate_state in(2,3) and Certificate_Id in @Certificate_Id)
                        update [Zor_Certificate] set Certificate_state =2
                        where Certificate_Id in @Certificate_Id";
            BaseAnswer bar = DapperEdit<T>.DoEdit(sql, new { Certificate_Id = id.ToArray<int>() });
            return bar;
        }
        /// <summary>
        /// 完税证启用信息-(未启用:0,已启用:1,已停用:2,已用完:3)
        /// create zhl by 2017.4.10
        /// </summary>
        /// <param name="id">启用的Id</param>
        /// <returns></returns>
        public static BaseAnswer CertificateStart(int id, int StartTaxNum, int EndTaxNum)
        {

            var dtime = TimeHelper.ToTimeStamp(DateTime.Now);
            var dtID = RequestSession.GetSessionUser().DT_Id;
            var us = RequestSession.GetSessionUser().User_Id;
            var sql = @"if exists(select * from Zor_Certificate where Certificate_StartTaxNum =  " + StartTaxNum + " and Certificate_EndTaxNum = " + EndTaxNum + " and Certificate_state = 0 or Certificate_state = 2 )  update [Zor_Certificate] set Certificate_state = 1,Certificate_ModifyTime= " + dtime + ",Certificate_ModifyName = "+us+" where Certificate_Id = " + id + "";

            BaseAnswer bar = DapperEdit<T>.DoEdit(sql, new { Certificate_Id = id });
            return bar;
        }
    }
}
